package com.szholly.pro.watergas_server.ps.android.controller;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.plug.safe.entity.role.RoleInitHelper;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.android.common.DateTime;
import com.szholly.pro.watergas_server.ps.android.common.NonNullUtil;
import com.szholly.pro.watergas_server.ps.android.common.ResultUtils;
import com.szholly.pro.watergas_server.ps.android.common.SecurityConfig;
import com.szholly.pro.watergas_server.ps.android.common.SqlUtils;
import com.szholly.pro.watergas_server.ps.android.common.SuffixCreator;
import com.szholly.pro.watergas_server.ps.android.common.TableRowMap;
import com.szholly.pro.watergas_server.ps.android.common.ValidUtils;
import com.szholly.pro.watergas_server.ps.android.service.UserService;
import com.szholly.pro.watergas_server.ps.data.OperationTaskRow;
import com.szholly.pro.watergas_server.ps.data.OperationTaskTable;
import com.szholly.pro.watergas_server.ps.data.PhoneSystemDataRow;
import com.szholly.pro.watergas_server.ps.data.PhoneSystemDataTable;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import net.sf.json.JSONObject;

/**
 * 公共模块管理器
 * 
 * 2017/3/30 <br/>
 *
 * @author Joey Huang QQ:273179370）
 */
public class AndroidCommonManager {
	private static AndroidCommonManager manager;
	QcService mQcDomainManager = QcService.get();

	private SuffixCreator suffixCreator = new SuffixCreator(4);

	public static AndroidCommonManager get() {
		if (manager == null) {
			manager = new AndroidCommonManager();
		}
		return manager;
	}

	/**
	 * 登录
	 */
	public String getLoginData(String userName, String password) throws Exception {
		ValidUtils.hasText(userName, "用户名不能为空");
		ValidUtils.hasText(password, "密码不能为空");
		RowBase userRowBase = getUserRow(userName);
		ValidUtils.notNull(userRowBase, "用户不存在");
		String realPassword = userRowBase.getSafeString("LOGIN_PASSWORD");
		ValidUtils.isTrue(password.equals(realPassword), "密码错误");
		Map<String, Object> resultMap = new HashMap<>();
		JSONObject userInfo = new JSONObject();
		String userId = userRowBase.getSafeString("ID");
		String name = userRowBase.getSafeString("NAME");
		String pic = userRowBase.getSafeString("PHOTO");
		String groupName = userRowBase.getSafeString("GROUP_NAME");
		String groupId = userRowBase.getSafeString("GROUP_ID");

		userInfo.put("name", name);// 添加用户信息
		userInfo.put("id", userId);
		userInfo.put("pic", pic);
		userInfo.put("name", name);
		userInfo.put("groupId", groupId);
		userInfo.put("groupName", groupName);

		if (!StringUtils.IsNullOrSpace(groupId)) {// 添加组信息
			userInfo.put("groupBean", getGroupRow(groupId));
		}

		resultMap.put("userInfo", userInfo); // 用户信息
		// 权限数据
		List<String> securitys = RoleInitHelper.getFunctions(userId);
		resultMap.put("securityList", NonNullUtil.toList(securitys));// 权限信息
		
		//注：如果没有所有公司查看权限，就必须属于实验公司人员，否则报错
		return ResultUtils.getToastResult(resultMap);
	}
	

	// 获取模块信息
	public String getModuleData(String userId) {
		String groupId = UserService.getGroupIdByUserId(userId);

		// 模块列表

		SecurityConfig securityConfig = SecurityConfig.get(userId);

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("sewage", AndroidConstant.MODULE_SEWAGE));
		parms.add(new GlobalParameter("supply", AndroidConstant.MODULE_SUPPLY));
		parms.add(new GlobalParameter("surface", AndroidConstant.MODULE_SURFACE));
		parms.add(new GlobalParameter("pollution_gas", AndroidConstant.MODULE_POLLUTION_GAS));
		parms.add(new GlobalParameter("pollution_water", AndroidConstant.MODULE_POLLUTION_WATER));
		parms.add(new GlobalParameter("qc", AndroidConstant.MODULE_QC));
		String sql = "";
		if (!StringUtils.IsNullOrSpace(groupId)) {
			parms.add(new GlobalParameter("groupId", groupId));

			if (securityConfig.containTypeSewage) {
				sql += "select count(t.exp_no) as allnum,'0' as station_category,@sewage as name　from (select t1.exp_no from BC_ENTERPRISE_ATTR t1 left join BC_SITE_INFO t2 on t2.wwtp_id=t1.exp_no "
						+ " left join OPERATION_GROUP_STATION_ASSO t3 on t2.exp_no=t3.station_id left join OC_GROUP t4 on t3.operation_group_id=t4.guid"
						+ "  where  t2.station_category='0' and t2.BUILD_STATUS='0' and t4.guid = @groupId group by t1.exp_no)t \n";
			}

			if (securityConfig.containTypeSurface) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.exp_no) as allnum,'1' as station_category,@surface as name　"
						+ " from (select t1.exp_no from  monitor_station t1  "
						+ "left join OPERATION_GROUP_STATION_ASSO t3 on t1.exp_no=t3.station_id "
						+ "left join OC_GROUP t4 on t3.operation_group_id=t4.guid  "
						+ "where t1.station_category='1' and t1.BUILD_STATUS='0' and t1.operation_unit = '深圳市浩瑞泰科技有限公司' and t4.guid= @groupId group by t1.exp_no)t \n";
			}

			if (securityConfig.containTypePollutionGas) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.exp_no) as allnum,'2' as station_category,@pollution_gas as name　"
						+ "from (select t1.exp_no from  monitor_station t1  "
						+ "left join OPERATION_GROUP_STATION_ASSO t3 on t1.exp_no=t3.station_id "
						+ "left join OC_GROUP t4 on t3.operation_group_id=t4.guid  "
						+ "where t1.station_category='4' and t1.BUILD_STATUS='0' and t4.guid= @groupId group by t1.exp_no)t \n";
			}
			if (securityConfig.containTypePollutionWater) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.exp_no) as allnum,'5' as station_category,@pollution_water as name　"
						+ "from (select t1.exp_no from  monitor_station t1  "
						+ "left join OPERATION_GROUP_STATION_ASSO t3 on t1.exp_no=t3.station_id "
						+ "left join OC_GROUP t4 on t3.operation_group_id=t4.guid  "
						+ "where t1.station_category='2' and t1.BUILD_STATUS='0' and t4.guid= @groupId group by t1.exp_no)t \n";
			}
			if (securityConfig.containTypeSupply) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.exp_no) as allnum,'3' as station_category,@supply as name　"
						+ "from (select t1.exp_no from  monitor_station t1  "
						+ "left join OPERATION_GROUP_STATION_ASSO t3 on t1.exp_no=t3.station_id "
						+ "left join OC_GROUP t4 on t3.operation_group_id=t4.guid  "
						+ "where t1.station_category='3' and t1.BUILD_STATUS='0' and t4.guid= @groupId group by t1.exp_no)t \n";
			}

			if (securityConfig.containTypeQc) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.companycode) as allnum,'4' as station_category,@qc as name from QC_COMPANYINFO t";
			}
		} else {
			if (securityConfig.containTypeSewage) {
				sql += "select count(t1.exp_no) as allnum,'0' as station_category,@sewage as name　from BC_ENTERPRISE_ATTR t1 where t1.poll_type='0' \n";
			}

			if (securityConfig.containTypeSurface) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t1.exp_no) as allnum,'1' as station_category,@surface as name from BC_SITE_INFO t1 where t1.station_category='1' and t1.BUILD_STATUS='0'  and t1.operation_unit = '深圳市浩瑞泰科技有限公司'\n";
			}

			if (securityConfig.containTypePollutionGas) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t1.exp_no) as allnum,'2' as station_category,@pollution_gas as name from BC_SITE_INFO t1 where t1.station_category='4' \n";
			}

			if (securityConfig.containTypePollutionWater) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t1.exp_no) as allnum,'5' as station_category,@pollution_water as name from BC_SITE_INFO t1 where t1.station_category='2' \n";
			}
			if (securityConfig.containTypeSupply) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t1.exp_no) as allnum,'3' as station_category,@supply as name from BC_SITE_INFO t1 where t1.station_category='3' and t1.build_status = '0' \n";
			}

			if (securityConfig.containTypeQc) {
				if (!StringUtils.IsNullOrSpace(sql)) {
					sql += "union all \n";
				}
				sql += "select count(t.companycode) as allnum,'4' as station_category,@qc as name from QC_COMPANYINFO t";
			}
		}
		if (StringUtils.IsNullOrSpace(sql)) {
			return ResultUtils.getToastResult("没有权限", false);
		}
		try {
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			if (rows != null && rows.size() > 0) {
				return ResultUtils.getToastResult(rows);
			} else {
				return ResultUtils.getToastResult("没有权限", false);
			}
		} catch (Exception e) {
			return ResultUtils.getToastResult(e);
		}
	}

	/**
	 * 获取用户信息
	 * 
	 * @param userId
	 *            when empty return null;
	 * @return
	 */
	public static RowBase getSafeUserRow(String userId) {
		if (StringUtils.IsNullOrSpace(userId)) {
			return null;
		}
		try {
			List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
			parms.add(new GlobalParameter("userId", userId));
			String sql = "select * from safe_user t where t.S_USERID = @userId";
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			if (rows != null && rows.size() > 0) {
				return rows.get(0);
			} else {
				return null;
			}
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 获取用户信息
	 * 
	 * @param loginName
	 *            账号
	 * 
	 * @return 包含字段｛LOGIN_NAME，LOGIN_PASSWORD，NAME，ID，PHOTO，GROUP_NAME，GROUP_ID，COMPANYCODE,COMPANYNAME｝
	 */
	private RowBase getUserRow(String loginName) {
		Assert.notNull(loginName, "账号不能为空");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("loginName", loginName));
		String sql = "select t.C_USERNAME    LOGIN_NAME,\n"
				+ "       t.C_PWD         LOGIN_PASSWORD,\n"
				+ "       t.C_USEREALNAME NAME,\n"
				+ "       t.S_USERID      ID,\n"
				+ "       t.PHOTO,\n"
				+ "       t3.GROUP_NAME,\n"
				+ "       t3.GUID         GROUP_ID,\n"
				+ "       t.S_ORGID       COMPANYCODE,\n"
				+ "       t.S_ORGNAME     COMPANYNAME\n"
				+ "  from SAFE_USER t\n"
				+ "  left join OC_GROUP_ASSO t2\n"
				+ "    on t.s_userid = t2.user_id\n"
				+ "  left join OC_GROUP t3\n"
				+ "    on t2.group_id = t3.guid\n"
				+ " WHERE 1 = 1\n"
				+ "   and t.C_USERNAME = @loginName";
		try {
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			if (rows != null && rows.size() > 0) {
				return rows.get(0);
			}
		} catch (Exception e) {
		}
		return null;
	}

	// 获取组信息
	private RowBase getGroupRow(String groupCode) throws Exception {
		ValidUtils.hasText(groupCode);
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("groupCode", groupCode));
		String sql = "select t.guid           \"id\",\n"
				+ "       t.group_name     \"name\",\n"
				+ "       t2.s_userid      \"groupLeaderId\",\n"
				+ "       t2.c_userealname \"groupLeaderName\"\n"
				+ "  from OC_GROUP t\n"
				+ "  left join OC_GROUP_ASSO t1\n"
				+ "    on t1.group_head = '1'\n"
				+ "   and t1.group_id = t.guid\n"
				+ "  left join SAFE_USER t2\n"
				+ "    on t2.s_userid = t1.user_id\n"
				+ " where t.guid = @groupCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			throw new SQLException("组信息获取失败！");
		}
		return rows.get(0);
	}

	private AndroidCommonManager() {
	}

	public String savePhoneSystemData(String userId, String deviceCode, String data) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("userId", userId));
		parms.add(new GlobalParameter("deviceCode", deviceCode));
		String sql = "select * from phone_system_data t where t.user_id = @userId and t.device_code = @deviceCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		// 频率
		String hz = "1";
		if (!CollectionUtils.isEmpty(rows)) {
			int preHz = rows.get(0).getSafeInt(PhoneSystemDataRow.FIELD_Hz);
			hz = String.valueOf(++preHz);
			String deleteSql = "delete from phone_system_data t where t.user_id = @userId and t.device_code = @deviceCode";
			DataUtil.beginTransaction();
			DataUtil.ExecuteUpdateSql(deleteSql, parms);
			DataUtil.commitTransaction();
		}
		JSONObject dataJson = JSONObject.fromObject(data);
		TableRowMap tableRowMap = TableRowMap.get(dataJson, PhoneSystemDataTable.TABLE_NAME);
		tableRowMap.put(PhoneSystemDataRow.FIELD_Hz, hz);
		SqlUtils.insert(tableRowMap);
		return ResultUtils.getToastResult("保存成功", true);
	}

	/**
	 * 添加单条运维任务
	 */
	public RowBase addOperationTask(String userId, String stationCode, String jobCode, String taskCodePrefix) throws Exception {
		ValidUtils.hasText(stationCode, "站点编号为空，运维任务添加失败");
		ValidUtils.hasText(userId, "用户编号为空，运维任务添加失败");
		ValidUtils.hasText(jobCode, "任务类型编号为空，运维任务添加失败");
		ValidUtils.hasText(taskCodePrefix, "任务编号前缀为空，运维任务添加失败");
		String taskCode = taskCodePrefix + new DateTime().toString("yyyyMMddHHmmss") + suffixCreator.createNumberString();
		OperationTaskRow row = new OperationTaskRow();
		row.setStationId(stationCode);
		row.setStartTime(new Date());
		row.setFinishBy(userId);
		row.setJobId(jobCode);
		row.setTaskCode(taskCode);
		row.setTaskStatus("0");
		SqlUtils.insert(OperationTaskTable.TABLE_NAME, row);
		return getOperationTask(taskCode);
	}

	public RowBase getOperationTask(String taskCode) throws Exception {
		ValidUtils.hasText(taskCode, "运维任务编号为空，获取失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("taskCode", taskCode));
		String sql = "select t.guid,\n"
				+ "       t.task_code,\n"
				+ "       to_char(t.start_time,'yyyy-mm-dd hh24:mi:ss')start_time,\n"
				+ "       to_char(t.end_time,'yyyy-mm-dd hh24:mi:ss')end_time,\n"
				+ "       t.finish_by,\n"
				+ "       t.task_desc,\n"
				+ "       t.job_id,\n"
				+ "       t.task_status,\n"
				+ "       t.station_id\n"
				+ "  from OP_TASK t\n"
				+ " where t.task_code = @taskCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			throw new Exception("运维任务编号有误，获取失败");
		}
		return rows.get(0);
	}

	public List<RowBase> getOperationTaskList(String userId, String stationCode, String jobCode) throws Exception {
		ValidUtils.hasText(stationCode, "站点编号为空，运维任务获取失败");
		ValidUtils.hasText(jobCode, "任务类型编号为空，运维任务获取失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("stationCode", stationCode));
		parms.add(new GlobalParameter("jobCode", jobCode));
		String sql = "select t.guid,\n"
				+ "       t.task_code,\n"
				+ "       to_char(t.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,\n"
				+ "       to_char(t.FINISH_TIME, 'yyyy-mm-dd hh24:mi:ss') end_time,\n"
				+ "       t.finish_by,\n"
				+ "       t.task_desc,\n"
				+ "       t.job_id,\n"
				+ "       t.task_status,\n"
				+ "       t.station_id\n"
				+ "  from OP_TASK t\n"
				+ " where t.station_id = @stationCode\n"
				+ "   and t.job_id = @jobCode\n";
		if (!StringUtils.IsNullOrSpace(userId)) {
			sql +="   and t.finish_by = @userId";
			parms.add(new GlobalParameter("userId", userId));
		}
		return DataUtil.getSelectRows(sql, parms);
	}

	public void deleteOperationTask(String taskCode) throws Exception {
		ValidUtils.hasText(taskCode, "运维任务编号为空，任务删除失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("taskCode", taskCode));
		String sql = "select * from OP_TASK t where t.task_code = @taskCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			throw new IllegalArgumentException("任务不存在,无法删除");
		}
		try {
			// 删除原有的数据
			DataUtil.beginTransaction();
			String deleteSql = "delete from OP_TASK t where t.task_code = @taskCode";
			DataUtil.ExecuteUpdateSql(deleteSql, parms);
			DataUtil.commitTransaction();
		} catch (Exception e) {
			DataUtil.rollbackTransaction();
			throw e;
		}
	}
}
